﻿using MySchool;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MyKTV
{
    public partial class chaxun_geshou_xinxi : Form
    {
        DBHelper db = new DBHelper();
        DataSet set = new DataSet();
        int pd;

        public chaxun_geshou_xinxi()
        {
            InitializeComponent();
        }

        private void chaxun_geshou_xinxi_Load(object sender, EventArgs e)
        {
            db.OpenConnection();
            string sql = "select * from singer_type";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection);
            adapter.Fill(set, "singer_type");
            DataRow row = set.Tables["singer_type"].NewRow();

            row[0] = -1;
            row[1] = "全部";
            set.Tables["singer_type"].Rows.InsertAt(row, 0);

            this.comboBox1.DataSource = set.Tables["singer_type"];
            this.comboBox1.ValueMember = "singertype_id";
            this.comboBox1.DisplayMember = "singertype_name";

            init();
            
        }

        private void init()
        {
            db.OpenConnection();
            string sql2 = "select singer_name,singer_type.singertype_name,singer_gender,singer_photo_url,singer_description from  singer_info inner join singer_type on singer_info.singertype_id=singer_type.singertype_id";
            SqlDataAdapter adapter2 = new SqlDataAdapter(sql2, db.Connection);
            adapter2.Fill(set, "singer_info");
            this.dataGridView1.DataSource = set.Tables["singer_info"];
            db.CloseConnection();
        }

        private void button1_Click(object sender, EventArgs e)
        {
           
            db.OpenConnection();
           
            string sql;

            if (this.comboBox1.Text=="全部" && this.textBox1.Text.Equals(string.Empty))
            {
                set.Tables["singer_info"].Clear();
                init();
                return;
            }
            else if (this.comboBox1.Text == "全部" && !this.textBox1.Text.Equals(string.Empty))
            {
                set.Tables["singer_info"].Clear();
                string sql2 = "select singer_name,singer_type.singertype_name,singer_gender,singer_photo_url,singer_description from  singer_info inner join singer_type on singer_info.singertype_id=singer_type.singertype_id where singer_name like N'%" + this.textBox1.Text + "%' ";
                SqlDataAdapter adapter2 = new SqlDataAdapter(sql2, db.Connection);
                adapter2.Fill(set, "singer_info");
                this.dataGridView1.DataSource = set.Tables["singer_info"];
                db.CloseConnection();
                return;
            }
            

            sql = "select * from singer_info where singer_name like N'%"+this.textBox1.Text+"%' ";
            SqlCommand comm = new SqlCommand(sql,db.Connection);
            int i =Convert.ToInt32( comm.ExecuteScalar());
            if (i<1 && this.textBox1.Text!="")
            {
                MessageBox.Show("没有该歌手信息","提示框",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }
            else
            {
                if (this.textBox1.Text.Trim().Equals(string.Empty) && this.comboBox1.Text != null)
                {
                    set.Tables["singer_info"].Clear();
                    sql = "select singer_name,singer_type.singertype_name,singer_gender,singer_photo_url,singer_description from  singer_info inner join singer_type on singer_info.singertype_id=singer_type.singertype_id where singertype_name like N'%" + this.comboBox1.Text + "%'";
                }
                else if (this.textBox1.Text.Trim() != "" && this.comboBox1.Text != null)
                {
                    set.Tables["singer_info"].Clear();
                    sql = "select singer_name,singer_type.singertype_name,singer_gender,singer_photo_url,singer_description from  singer_info inner join singer_type on singer_info.singertype_id=singer_type.singertype_id where singer_type.singertype_name like N'%" + this.comboBox1.Text + "%'and singer_name like N'%" + this.textBox1.Text + "%'";
                }
                else if (this.textBox1.Text.Trim() != "" && this.comboBox1.Text == null)
                {
                    set.Tables["singer_info"].Clear();
                    sql = "select singer_name,singer_type.singertype_name,singer_gender,singer_photo_url,singer_description from  singer_info inner join singer_type on singer_info.singertype_id=singer_type.singertype_id where singer_name like N'%" + this.textBox1.Text + "%'";
                }
                else
                {
                    MessageBox.Show("格式有误");
                    return;
                }
                SqlDataAdapter adapter = new SqlDataAdapter(sql, db.Connection);
                adapter.Fill(set, "singer_info");
                this.dataGridView1.DataSource = set.Tables["singer_info"];
                db.CloseConnection();

            }




        }
        
        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            
            DialogResult result= MessageBox.Show("确定要删除吗？", "警示框", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

            if (result==DialogResult.Yes)
            {
                db.OpenConnection();
                string i = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                string sql = "delete from singer_info where singer_name=N'" + i + "' ";
                SqlCommand comm = new SqlCommand(sql, db.Connection);
                int fh = comm.ExecuteNonQuery();
                if (fh > 0)
                {
                    MessageBox.Show("删除成功");
                    set.Tables["singer_info"].Clear();
                    init();
                }
            }

            
            
            
            db.CloseConnection();
        }

        

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            



            update date = new update();
            date.i= dataGridView1.SelectedRows[0].Cells["singer_name"].Value.ToString();
            date.e = dataGridView1.SelectedRows[0].Cells["singer_gender"].Value.ToString();
            date.s = dataGridView1.SelectedRows[0].Cells["singertype_name"].Value.ToString();
            date.w= dataGridView1.SelectedRows[0].Cells["singer_description"].Value.ToString();
            date.tup = dataGridView1.SelectedRows[0].Cells["singer_photo_url"].Value.ToString();
            

            if (dataGridView1.SelectedRows[0].Cells["singer_gender"].Value.ToString()=="男")
            {
                date.xb = 1;
            }
            else if(dataGridView1.SelectedRows[0].Cells["singer_gender"].Value.ToString() == "女")
            {
                date.xb = 2;
            }
            else if (dataGridView1.SelectedRows[0].Cells["singer_gender"].Value.ToString() == "组合")
            {
                date.xb = 3;
            }



            date.ShowDialog();
           
        }
    }
}
